package org.box.demos.multithreading.wgetmt.core.storage;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.Collection;
import java.util.LinkedHashSet;
import java.util.Set;

import org.box.demos.multithreading.wgetmt.Configuration;
import org.box.demos.multithreading.wgetmt.core.ContentType;
import org.box.demos.multithreading.wgetmt.core.Storage;
import org.box.demos.multithreading.wgetmt.core.URL;
import org.box.demos.multithreading.wgetmt.j16mt.ProgressMonitor;

public class DbStorage implements Storage {
	
	private static final String DOWNLOAD_STATE_INCOMPLATE = "I";
	private static final String DOWNLOAD_STATE_COMPATED = "C";
	
	private static final String SQL_INSERT = "INSERT INTO pages (url, content_type, state, content, size) VALUES (?,?,?,?,?)";
	private static final String SQL_UPDATE = "UPDATE pages SET content_type = ?, state = ?, content = ?, size = ? WHERE url = ?";

	private static final String SQL_SELECT_BY_URL = "SELECT * FROM pages WHERE url = ?";
	private static final String SQL_SELECT_BY_STATE = "SELECT * FROM pages WHERE state = ?";
	
	private Connection conn;
	private final FileStorage fileStorage = new FileStorage();

	@Override
	public void connect(Configuration cfg) throws IOException {
		fileStorage.connect(cfg);
		try {
			Class.forName("org.hsqldb.jdbcDriver");
			conn = DriverManager.getConnection("jdbc:hsqldb:" + cfg.getDatabaseName() + ";shutdown=true", "sa", "");
			
			conn.setAutoCommit(false);
			Statement stat = null;
			try {
				stat = conn.createStatement();
				stat.execute("SET FILES LOG FALSE");
				
				stat.execute("CREATE TABLE pages (url VARCHAR(1024), content_type CHAR(1), state CHAR(1), content BINARY, size INT)");
			} catch (SQLException e) {
				// hack
			} finally {
				if (stat != null)
					stat.close();
				
			}
		} catch (ClassNotFoundException e) {
			throw new RuntimeException();
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
		}
	}
	
	@Override
	public void disconnect() throws IOException {
		if (conn != null) {
			try {
				conn.commit();
			} catch (SQLException e) {
				throw new IOException(e);
			} finally {
				try {
					conn.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
			}
		}
	}
	
	@Override
	public void write(URL url, byte[] content)
			throws IOException {
		
		fileStorage.write(url, content);
		PreparedStatement stat = null;
		try {
			stat = conn.prepareStatement(SQL_UPDATE);
			stat.setString(1, ""+ url.getContentType().shortVal());
			stat.setString(2, getStateFlag(content, url.getContentType()));
			stat.setNull(3, Types.BLOB);
			stat.setInt(4, content != null ? content.length : -1);
			stat.setString(5, url.toString());
			stat.execute();
			
			if (stat.getUpdateCount() <= 0) {
				stat.close();
				stat = conn.prepareStatement(SQL_INSERT);
				stat.setString(1, url.toString());
				stat.setString(2, ""+ url.getContentType().shortVal());
				stat.setString(3, getStateFlag(content, url.getContentType()));
				stat.setNull(4, Types.BLOB);
				stat.setInt(5, content != null ? content.length : -1);
				stat.execute();
			}
			
			conn.commit();
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (stat != null)
				try {
					stat.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
		}
	}

	private String getStateFlag(byte[] content, ContentType contentType) {
		return content != null 
				&& contentType != null 
				&& contentType != ContentType.UNKNOWN ? DOWNLOAD_STATE_COMPATED : DOWNLOAD_STATE_INCOMPLATE;
	}

	@Override
	public String read(URL url) throws IOException {
		return fileStorage.read(url);
	}

	@Override
	public boolean convert(URL url) throws IOException {
		return fileStorage.convert(url);
	}
	
	@Override
	public Collection<URL> loadIncomplete() throws IOException {
		Set<URL> set = new LinkedHashSet<URL>();
		
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(SQL_SELECT_BY_STATE);
			stat.setString(1, DOWNLOAD_STATE_INCOMPLATE);
				rs = stat.executeQuery();
				while (rs.next()) {
					set.add(new URL(rs.getString(1)));
				}
				rs.close();
			
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (stat != null)
				try {
					stat.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
		}

		return set;
	}

	@Override
	public void convertLinks(ProgressMonitor pm) throws IOException {

		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(SQL_SELECT_BY_URL);
			stat.setString(1, ContentType.TEXT_HTML.shortVal());
				rs = stat.executeQuery();
				while (rs.next()) {
					URL url = new URL(rs.getString(1));
					if (convert(url)) {
						pm.onContentConverted(url);
					}
				}
				rs.close();
			
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (stat != null)
				try {
					stat.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
		}
		
	}

	@Override
	public Set<URL> addUrls(Set<URL> newUrls) throws IOException {

		Set<URL> set = new LinkedHashSet<URL>();
		
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(SQL_SELECT_BY_URL);
			for (URL url : newUrls) {
				stat.setString(1, url.toString());
				rs = stat.executeQuery();
				if (!rs.next()){
					set.add(url);
				}
				rs.close();
			}
			stat.close();
			
			if (set.size() > 0) {
				stat = conn.prepareStatement(SQL_INSERT);
				for (URL e : set) {
					
					stat.setString(1, e.toString());
					stat.setString(2, ""+ContentType.UNKNOWN.shortVal());
					stat.setString(3, DOWNLOAD_STATE_INCOMPLATE);
					stat.setNull(4, Types.BLOB);
					stat.setInt(5, -1);
					stat.addBatch();
				}
				stat.executeBatch();
			}
			
			conn.commit();

		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (stat != null)
				try {
					stat.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
		}
		
		return set;
	}

	@Override
	public boolean containsUrl(URL u) throws IOException {
		
		String urlStr =  URL.removeInnerPath(u.toString());
		PreparedStatement stat = null;
		ResultSet rs = null;
		try {
			stat = conn.prepareStatement(SQL_SELECT_BY_URL);
			stat.setString(1, urlStr);
			rs = stat.executeQuery();
			if (rs.next()){
				if (rs.next())
					System.err.println("Warning: Duplicated rows have been detected for URL: "+ urlStr);
				return true;
			}
		} catch (SQLException e) {
			throw new IOException(e);
		} finally {
			if (stat != null)
				try {
					stat.close();
				} catch (SQLException e) {
					throw new IOException(e);
				}
		}
		return false;
	}

}
